const mysql = require('mysql');
const { use } = require('../express/12apiRouter');

const db = mysql.createPool({
    host:'localhost',
    user:'root',
    password:'123456',
    database:'node_study_db'
})

// 测试 mysql 能否正常工作
// db.query('SELECT 1',(err,result)=>{
//     if(err) return console.log(err.message);
//     // 只要能打印出来 [{RowDataPacket {'1':1}}] 的结果，就证明数据库连接正常
//     console.log(result);
// })
// 查询
const sqlStr = 'select * from user'
db.query(sqlStr,(err,result)=>{
    if(err) console.log(err.message);
    console.log(result);
})


// 插入
const user = {name:'lzj',age:18}

// const sqlStr1 = "INSERT INTO user (name,age) VALUES (?,?)"
const sqlStr1 = "INSERT INTO user SET ?"

// db.query(sqlStr1,[user.name,user.age],(err,result)=>{
//     if(err) console.log(err.message);
//     if(result.affectedRows == 1){
//         console.log('插入数据成功');
//     }
// })
db.query(sqlStr1,user,(err,result)=>{
    if(err) console.log(err.message);
    if(result.affectedRows == 1){
        console.log('插入数据成功');
    }
})


// 更新
const newUser = {id:6,name:'ls',age:21}
// const sqlStr2 = "UPDATE user SET name = ?,age=? WHERE id = ?"
// db.query(sqlStr2,[newUser.name,newUser.age,newUser.id],(err,result)=>{
//     if(err) console.log(err.message);
//     if(result.affectedRows == 1){
//         console.log('更新数据成功');
//     }
// })
const sqlStr2 = "UPDATE user SET ? WHERE id = ?"
db.query(sqlStr2,[newUser,newUser.id],(err,result)=>{
    if(err) console.log(err.message);
    if(result.affectedRows == 1){
        console.log('更新数据成功');
    }
})

// 删除
const sqlStr3 = 'DELETE FROM user WHERE id = ?'
db.query(sqlStr3,1,(err,result)=>{
    if(err) console.log(err.message);
    if(result.affectedRows === 1){
        console.log('删除数据成功');
    }
})
